import org.intellij.lang.annotations.Language;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;


/**
 * реализовать добавление в БД объектов
 * и все будет сделано
 */

public class louding_to_DB  {
    private PreparedStatement pr_sql;



    
    public void addAttr_Object_types(String obj_type, String[] attr, Connection con) throws SQLException {
        @Language("Oracle") String sql_req_atr_type = "merge into ATTR_OBJECT_TYPES Aot using (select " +
                "(select get_by_name.getattr_id(?) as NEWID from dual)" +
                " ATTR_ID, " +
                " (select get_by_name.getonj_type_id(?) as NEWID from dual) OBJECT_TYPE_ID, " +
                "? SHOW_ORDER from dual) dAot " +
                "on ( Aot.ATTR_ID = dAot.ATTR_ID " +
                " and Aot.OBJECT_TYPE_ID = dAot.OBJECT_TYPE_ID ) " +
                " when not matched " +
                "then insert " +
                " (Aot.ATTR_ID, Aot.OBJECT_TYPE_ID, Aot.SHOW_ORDER) " +
                " values ( dAot.ATTR_ID, dAot.OBJECT_TYPE_ID, dAot.SHOW_ORDER ) ";
        pr_sql = con.prepareStatement(sql_req_atr_type);
        pr_sql.setString(2, obj_type);
        pr_sql.setInt(3, 0);
        for (String attribut : attr){
            pr_sql.setString(1, attribut);
            
            try{
                pr_sql.executeUpdate();
            } catch (SQLException sq){
                sq.printStackTrace();
            }
            
            
        }
        
        
    }
    
   public void addParams(String[] params, String[] attrs, String object, Connection con) throws SQLException {
       
       @Language("Oracle") String sql_req_params = "merge into PARAMS Pr using (select (select get_by_name.getobj_id(?) as NEWID from dual)  OBJECT_ID, " +
               " get_by_name.getattr_id(?) ATTR_ID, ? VALUE from dual ) dPr " +
               "on ( Pr.OBJECT_ID = dPr.OBJECT_ID and Pr.ATTR_ID = dPr.ATTR_ID ) " +
               " when matched " +
               "then update  set Pr.VALUE = dPr.VALUE" +
               " where (Pr.OBJECT_ID = dPr.OBJECT_ID and Pr.ATTR_ID = dPr.ATTR_ID)" +
               " when not matched " +
               "then insert " +
               " (Pr.OBJECT_ID, Pr.ATTR_ID, Pr.VALUE) values " +
               " ( dPr.OBJECT_ID, dPr.ATTR_ID, dPr.VALUE ) ";
       pr_sql = con.prepareStatement(sql_req_params);
       pr_sql.setString(1, object);
       for(int i = 0; i < attrs.length; i++){
           pr_sql.setString(2, attrs[i]);
           pr_sql.setString(3, params[i]);
           try{
               pr_sql.executeUpdate();
           } catch (SQLException sq){
               sq.printStackTrace();
           }

           
       }
       
   }
    
   public void addObjec_types(String[] obj_types, Connection con) throws SQLException {
       @Language("Oracle") String sql_req_obj_type = "merge into OBJECT_TYPES Ot using (select  " +
               "(select getpac.getid as NEWID from dual) OBJECT_TYPE_ID,"+
               " ?  NAME from dual ) dOt " +
               "on ( Ot.NAME = dOt.NAME )" +
               " when not matched " +
               "then insert " +
               " ( Ot.OBJECT_TYPE_ID, Ot.NAME ) values ( dOt.OBJECT_TYPE_ID, dOt.NAME )";

       pr_sql = con.prepareStatement(sql_req_obj_type);
       for (String obj_type : obj_types){


           pr_sql.setString(1, obj_type);
           try{
               pr_sql.executeUpdate();
           } catch (SQLException sq){
               sq.printStackTrace();
           }
           
       }
       
       
   }
    
   public void addAttributes(String[] attrs, Connection con) throws SQLException {
       @Language("Oracle") String sql_req_atr = "merge into ATTRIBUTES att using ( select " +
               "(select getpac.getid as NEWID from dual) attr_id, ? NAME from dual ) dAt " +
               "on ( att.NAME = dAt.NAME )" +
               " when not matched " +
               "then insert " +
               " (att.ATTR_ID, att.NAME) values ( dAt.attr_id, dAt.NAME )";
       pr_sql = con.prepareStatement(sql_req_atr);
       for(String attribut : attrs){
           pr_sql.setString(1, attribut);
           try{
               pr_sql.executeUpdate();
           } catch (SQLException sq){
               sq.printStackTrace();
           }
           
       }
       
   }

   public void addObject(String[] objects, String Object_type, Connection con) throws SQLException {
       @Language("Oracle") String sql_req_obj = "merge into OBJECTS Ob using (select " +
               "(select getpac.getid as NEWID from dual) OBJECT_ID,  (select get_by_name.getonj_type_id(?) as NEWID from dual)" +
               "OBJECT_TYPE_ID, ? NAME from dual) dO " +
               "on ( Ob.OBJECT_TYPE_ID = dO.OBJECT_TYPE_ID" +
               "  and Ob.NAME = dO.NAME )" +
               " when not matched then insert " +
               " (Ob.OBJECT_ID, Ob.OBJECT_TYPE_ID, Ob.NAME) values (dO.OBJECT_ID," +
               " dO.OBJECT_TYPE_ID,  dO.NAME)";
       pr_sql = con.prepareStatement(sql_req_obj);
       for (String object: objects ){
           pr_sql.setString(1, Object_type);
           pr_sql.setString(2, object);
           try{
               pr_sql.executeUpdate();
           } catch (SQLException sq){
               sq.printStackTrace();
           }
           
           
           
       }
       
       
   }




}
